using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
	/// <summary> 
	///Author: nnamthach@gmail.com 
	/// <summary>
    public class UserDataAccess : Connection
    {
		#region Local Variable
        private string _strSPInsertName = "dbo.[procUser_add]";
        private string _strSPUpdateName = "dbo.[procUser_update]";
        private string _strSPDeleteName = "dbo.[procUser_delete]";
        private string _strSPGetName = "dbo.[procUser_get]";
        private string _strSPGetName_ByUsername = "dbo.[procUser_get_ByUsername]";
        private string _strSPGetName_Test_ByUsername = "dbo.[procUser_get_Test_ByUsername]";
        private string _strSPGetAllName = "dbo.[procUser_getall]";
		private string _strSPGetPages = "dbo.[procUser_getpaged]";
		private string _strSPIsExist = "dbo.[procUser_isexist]";
        private string _strTableName = "tblUser";
		#endregion Local Variable
		
		#region Method
        public UserInfo Get_User(
			Int64 ID
		, ref string sErr)
        {
			UserInfo objEntr = new UserInfo();
			connect();
			InitSPCommand(_strSPGetName);              
			AddParameter("ID", ID);
            
            DataTable list = new DataTable();
            try
            {                
                list = executeSelectSP(command);                
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();  
            
            if (list.Rows.Count > 0)
                objEntr = (UserInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }
        public UserInfo Get_User_ByUsername(
           String username
       , ref string sErr)
        {
            UserInfo objEntr = new UserInfo();
            connect();
            InitSPCommand(_strSPGetName_ByUsername);
            AddParameter("Username", username);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            if (list.Rows.Count > 0)
                objEntr = (UserInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable Get_User_Test_ByUsername(
          String username
      , ref string sErr)
        {
            UserInfo objEntr = new UserInfo();
            connect();
            InitSPCommand(_strSPGetName_Test_ByUsername);
            AddParameter("Username", username);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            //if (list.Rows.Count > 0)
            //    objEntr = (UserInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            UserInfo result = new UserInfo();
			result.ID = (dt.Rows[i]["ID"] == DBNull.Value ? -1 : (Int64)dt.Rows[i]["ID"]);
			result.Username = (dt.Rows[i]["Username"] == DBNull.Value ? "" : (String)dt.Rows[i]["Username"]);
			result.Password = (dt.Rows[i]["Password"] == DBNull.Value ? "" : (String)dt.Rows[i]["Password"]);
			result.Email = (dt.Rows[i]["Email"] == DBNull.Value ? "" : (String)dt.Rows[i]["Email"]);
			result.Regdate = (dt.Rows[i]["Regdate"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["Regdate"]);
			result.Ip = (dt.Rows[i]["Ip"] == DBNull.Value ? "" : (String)dt.Rows[i]["Ip"]);
			result.Firstname = (dt.Rows[i]["Firstname"] == DBNull.Value ? "" : (String)dt.Rows[i]["Firstname"]);
			result.Lastname = (dt.Rows[i]["Lastname"] == DBNull.Value ? "" : (String)dt.Rows[i]["Lastname"]);
			result.Birthdate = (dt.Rows[i]["Birthdate"] == DBNull.Value ? -1 : (Int32)dt.Rows[i]["Birthdate"]);
			result.Birthplace = (dt.Rows[i]["Birthplace"] == DBNull.Value ? "" : (String)dt.Rows[i]["Birthplace"]);
			result.Ssn = (dt.Rows[i]["Ssn"] == DBNull.Value ? "" : (String)dt.Rows[i]["Ssn"]);
            result.GroupCode = (dt.Rows[i]["GroupCode"] == DBNull.Value ? "" : (String)dt.Rows[i]["GroupCode"]);
			result.ANAL_U0 = (dt.Rows[i]["ANAL_U0"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_U0"]);
			result.ANAL_U1 = (dt.Rows[i]["ANAL_U1"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_U1"]);
			result.ANAL_U2 = (dt.Rows[i]["ANAL_U2"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_U2"]);
			result.ANAL_U3 = (dt.Rows[i]["ANAL_U3"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_U3"]);
			result.ANAL_U4 = (dt.Rows[i]["ANAL_U4"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_U4"]);
			result.ANAL_U5 = (dt.Rows[i]["ANAL_U5"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_U5"]);
			result.ANAL_U6 = (dt.Rows[i]["ANAL_U6"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_U6"]);
			result.ANAL_U7 = (dt.Rows[i]["ANAL_U7"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_U7"]);
			result.ANAL_U8 = (dt.Rows[i]["ANAL_U8"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_U8"]);
			result.ANAL_U9 = (dt.Rows[i]["ANAL_U9"] == DBNull.Value ? "" : (String)dt.Rows[i]["ANAL_U9"]);
           
            return result;
        }

        public DataTable GetAll_User(ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		/// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_User(UserInfo objEntr, ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPInsertName);           
			AddParameter("Username", objEntr.Username);
			AddParameter("Password", objEntr.Password);
			AddParameter("Email", objEntr.Email);
			AddParameter("Regdate", objEntr.Regdate);
			AddParameter("Ip", objEntr.Ip);
			AddParameter("Firstname", objEntr.Firstname);
			AddParameter("Lastname", objEntr.Lastname);
			AddParameter("Birthdate", objEntr.Birthdate);
			AddParameter("Birthplace", objEntr.Birthplace);
            AddParameter("GroupCode", objEntr.GroupCode);
			AddParameter("Ssn", objEntr.Ssn);
			AddParameter("ANAL_U0", objEntr.ANAL_U0);
			AddParameter("ANAL_U1", objEntr.ANAL_U1);
			AddParameter("ANAL_U2", objEntr.ANAL_U2);
			AddParameter("ANAL_U3", objEntr.ANAL_U3);
			AddParameter("ANAL_U4", objEntr.ANAL_U4);
			AddParameter("ANAL_U5", objEntr.ANAL_U5);
			AddParameter("ANAL_U6", objEntr.ANAL_U6);
			AddParameter("ANAL_U7", objEntr.ANAL_U7);
			AddParameter("ANAL_U8", objEntr.ANAL_U8);
			AddParameter("ANAL_U9", objEntr.ANAL_U9);
          
            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if(tmp != null && tmp != DBNull.Value)
					ret = Convert.ToInt32(tmp);
				else 
					ret=0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
			
            return ret;
        }

        public string Update_User(UserInfo objEntr)
        {

            connect();
            InitSPCommand(_strSPUpdateName); 
                       
			AddParameter("ID", objEntr.ID);
			AddParameter("Username", objEntr.Username);
			AddParameter("Password", objEntr.Password);
			AddParameter("Email", objEntr.Email);
			AddParameter("Regdate", objEntr.Regdate);
			AddParameter("Ip", objEntr.Ip);
			AddParameter("Firstname", objEntr.Firstname);
			AddParameter("Lastname", objEntr.Lastname);
			AddParameter("Birthdate", objEntr.Birthdate);
			AddParameter("Birthplace", objEntr.Birthplace);
			AddParameter("Ssn", objEntr.Ssn);
            AddParameter("GroupCode", objEntr.GroupCode);
			AddParameter("ANAL_U0", objEntr.ANAL_U0);
			AddParameter("ANAL_U1", objEntr.ANAL_U1);
			AddParameter("ANAL_U2", objEntr.ANAL_U2);
			AddParameter("ANAL_U3", objEntr.ANAL_U3);
			AddParameter("ANAL_U4", objEntr.ANAL_U4);
			AddParameter("ANAL_U5", objEntr.ANAL_U5);
			AddParameter("ANAL_U6", objEntr.ANAL_U6);
			AddParameter("ANAL_U7", objEntr.ANAL_U7);
			AddParameter("ANAL_U8", objEntr.ANAL_U8);
			AddParameter("ANAL_U9", objEntr.ANAL_U9);
               
            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_User(
			Int64 ID
		)
        {
            connect();
            InitSPCommand(_strSPDeleteName);                        
			AddParameter("ID", ID);
              
            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }   
		
		public DataTableCollection Get_Page(UserInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
        {
			string whereClause = CreateWhereClause(obj);
            DataTableCollection dtList = null;
            connect();
            InitSPCommand(_strSPGetPages); 
          
            AddParameter("WhereClause", whereClause);
            AddParameter("OrderBy", orderBy);
            AddParameter("PageIndex", pageIndex);
            AddParameter("PageSize", pageSize);           
            
            try
            {                
                dtList = executeCollectSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return dtList;
        }
        
        public Boolean IsExist_User(
			Int64 ID
		)
        {
            connect();
            InitSPCommand(_strSPIsExist);
                       
			AddParameter("ID", ID);
              
            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            if(list.Rows.Count==1)
				return true;
            return false;
        }
		
		private string CreateWhereClause(UserInfo obj)
        {
            String result = "";

            return result;
        }
        
        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            connect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
		#endregion Method
     
    }
}
